package com.shxt.lesson29.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Map;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class DBManager{
    
    private Connection conn;
    private Statement stmt;
    
    public DBManager(String dbName, String userName, String password)
    throws Exception{
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            String url = "jdbc:mysql://localhost:3306/" + dbName + "?useSSL=false";
            conn = DriverManager.getConnection(url, userName, password);
            stmt = conn.createStatement();
        }catch(Exception e){
            throw e;
        }
    }
    
    public void insert(String tableName, Map<String, Object> map)
    throws Exception{        
        String fieldNames = "(" + String.join(", ", map.keySet()) + ")";
        
        String[] valuesStr = new String[map.size()];
        int i = 0;
        for(Object item : map.values()){
            valuesStr[i++] = item.toString();
        }
        String values = "(" + String.join(", ", valuesStr) + ")";
        
        String sql = "insert into " + tableName + " " + fieldNames + " values " + values;
        
        try{
            stmt.executeUpdate(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public ResultSet select(String tableName, Map<String, Object> map)
    throws Exception{
        try{
            String[] args = new String[map.size()];
            int i = 0;
            for(Map.Entry<String, Object> entry : map.entrySet()){
                args[i++] = "" + entry.getKey() + " = " + entry.getValue();
            }
            
            String whereArg = "where " + String.join(" and ", args);
            String sql = "select * from " + tableName;
            sql += " " + whereArg;
            
            return stmt.executeQuery(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int update(String tableName, Map<String, Object> setMap, Map<String, Object> whereMap)
    throws Exception{
        try{
            String[] args_set = new String[setMap.size()];
            int i = 0;
            for(Map.Entry<String, Object> entry : setMap.entrySet()){
                args_set[i++] = "" + entry.getKey() + " = " + entry.getValue();
            }
            String setArg = "set " + String.join(", ", args_set);
            
            String[] args_where = new String[whereMap.size()];
            i = 0;
            for(Map.Entry<String, Object> entry : whereMap.entrySet()){
                args_where[i++] = "" + entry.getKey() + " = " + entry.getValue();
            }
            String whereArg = "where " + String.join(" and ", args_where);
            
            String sql = "update " + tableName + " " + setArg + " " + whereArg;
            
            return stmt.executeUpdate(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public String[] getColumnNames(String tableName)
    throws Exception{
        try{
            ResultSet rs = stmt.executeQuery("select * from " + tableName + " limit 0");
            ResultSetMetaData rsmd = rs.getMetaData();
            
            int numberOfColumns = rsmd.getColumnCount();
            String[] columnNames = new String[numberOfColumns];
            for(int i = 0; i < columnNames.length; i++){
                columnNames[i] = rsmd.getColumnName(i + 1);
            }
            return columnNames;
        }catch(Exception e){
            throw e;
        }
    }
    
    public List<Map<String, Object>> getAllData(String tableName)
    throws Exception{
        try{
            ResultSet rs = stmt.executeQuery("select * from " + tableName);
            ResultSetMetaData rsmd = rs.getMetaData();  
                      
            int numberOfColumns = rsmd.getColumnCount();
            String[] columnNames = new String[numberOfColumns];
            for(int i = 0; i < columnNames.length; i++){
                columnNames[i] = rsmd.getColumnName(i + 1);
            }
            
            List<Map<String, Object>> rsList = new ArrayList<Map<String, Object>>();
            while(rs.next()){
                Map<String, Object> rsMap = new HashMap<String, Object>();
                for(int i = 0; i < columnNames.length; i++){
                    String key = columnNames[i];
                    Object value = rs.getObject(columnNames[i]);
                    rsMap.put(key, value);
                }
                rsList.add(rsMap);
            }
            
            return rsList;
        }catch(Exception e){
            throw e;
        }
    }
    
    public List<Map<String, Object>> executeQuery(String sql)
    throws Exception{
        try{
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();  
                      
            int numberOfColumns = rsmd.getColumnCount();
            String[] columnNames = new String[numberOfColumns];
            for(int i = 0; i < columnNames.length; i++){
                columnNames[i] = rsmd.getColumnName(i + 1);
            }
            
            List<Map<String, Object>> rsList = new ArrayList<Map<String, Object>>();
            while(rs.next()){
                Map<String, Object> rsMap = new HashMap<String, Object>();
                for(int i = 0; i < columnNames.length; i++){
                    String key = columnNames[i];
                    Object value = rs.getObject(columnNames[i]);
                    rsMap.put(key, value);
                }
                rsList.add(rsMap);
            }
            
            return rsList;
        }catch(Exception e){
            throw e;
        }
    }
    
    public int executeUpdate(String sql)
    throws Exception{
        try{
            return stmt.executeUpdate(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public void close()
    throws Exception{
        try{
            stmt.close();
            conn.close();
        }catch(Exception e){
            throw e;
        }
    }
    
}